import random
import pandas as pd

"""
生成【期末报告】成绩
预期成绩为课堂纪律平均分-10分
"""

# 解决数据输出时列名不对齐的问题
pd.set_option('display.unicode.east_asian_width', True)


def round_to_nearest_five(num):
    return 5 * (num // 5)


def generate_random_number(average):
    # 计算标准差
    std_dev = average / 10
    # 生成随机数
    random_number = random.gauss(average, std_dev)
    # 确保结果是整数
    n = int(round(random_number))
    return round_to_nearest_five(n)


def make(file, sheet, threshold=10, n=1):
    print(f"【期末报告】预期成绩为：课堂纪律平均分-{threshold}")
    df = pd.read_excel(file, sheet_name=sheet, dtype={
        '序号': 'int',
        '学号': 'str',
        '姓名': 'str',
        '课堂纪律': 'int'
    })
    df = df[['序号', '学号', '姓名', '课堂纪律']]

    df['预期均值'] = df['课堂纪律'] - threshold
    # 预期0分的成绩保持0分
    df.loc[df['课堂纪律'] == 0, '预期均值'] = 0

    for i in range(1, 1 + n):
        column = f'第{i}次'

        # 产生原始随机成绩
        df[column] = df['预期均值'].apply(generate_random_number)

        # 预期分和随机分的分差不要超过15
        df.loc[df['预期均值'] - df[column] >= threshold, column] = round_to_nearest_five(
            df['预期均值'] - threshold)  # 随机分过低
        df.loc[df[column] - df['预期均值'] >= threshold, column] = round_to_nearest_five(
            df['预期均值'] + threshold)  # 随机分过高

        # 所有成绩确保不高于95分
        df.loc[df[column] >= 95, column] = 95
        # 所有成绩确保不低于65分
        df.loc[df[column] < 65, column] = 65
        # 预期0分的成绩保持0分
        df.loc[df['预期均值'] == 0, column] = 0

    df.insert(5, '实际均值', df.loc[:, '第1次':].mean(axis=1))
    df.insert(6, '均值差', df['预期均值'] - df['实际均值'])

    fix(df)
    # print(df)

    with pd.ExcelWriter(file, mode='a', if_sheet_exists='new') as writer:
        df.to_excel(writer, sheet_name='期末报告', index=False)


def fix(df):
    df['第1次'] = round(2 * (df['第1次'] * 0.4 // 2)) - random.randint(1, 4)
    df['第2次'] = round(3 * (df['第2次'] * 0.3 // 3)) - random.randint(1, 3)
    df['第3次'] = round(3 * (df['第3次'] * 0.2 // 3)) - random.randint(1, 3)
    df['第4次'] = round(1 * (df['第4次'] * 0.1 // 1)) - random.randint(1, 3)

    df.loc[df['第1次'] < 24, '第1次'] = 24
    df.loc[df['第2次'] < 18, '第2次'] = 18
    df.loc[df['第3次'] < 12, '第3次'] = 12
    df.loc[df['第4次'] < 6, '第4次'] = 6
    df.loc[df['第4次'] > 8, '第4次'] = 8
    df['总分'] = df['第1次'] + df['第2次'] + df['第3次'] + df['第4次']
